# -*- coding: utf-8 -*-            
# @Author : chenliang
# @Time : 2025/7/7 14:18

from Common.get_sql_object import GetSqlObject
from Common.business_common_tool import BusinessCommonTools
from Common.excel import excel
import openpyxl
import os


#oo = excel("/Users/chenliang/Desktop/司机车辆通用导入模板 (普通批量).xlsx")
#oo = excel("/Users/chenliang/Desktop/司机信息.xlsx")
excelpath = input('目标excel文件地址:')
oo = excel(excelpath)

 #分割路径为目录和文件名
directory, filename = os.path.split(excelpath)

# 分割文件名和扩展名
name_without_ext, ext = os.path.splitext(filename)

# 在文件名末尾添加 "__副"
new_filename = f"{name_without_ext}__副{ext}"

# 重新组合新的路径
new_path = os.path.join(directory, new_filename)



result=oo.getExcelData('0','司机手机号','车牌号')
i=oo.findIndex(0,['驾驶证发证机关','行驶证发证机关','从业资格证号','道路运输证号'])

print(result)
print(i)

workbook = openpyxl.load_workbook(filename=excelpath)
num=2
for a in result[0]:
    sql = """SELECT
	t.driving_licence_issuing_authority,
	b.issuing_authority,
	t.cyzg_code,
	b.dlys_code 
FROM
	d_driver t
	LEFT JOIN d_car_manage_info b on t.id = b.user_id 
WHERE
	 t.phone = '@a'
	and b.license_code='@b' and b.status='2' LIMIT 1;"""

    mysql_source = GetSqlObject.get_instance("PRO")
    driver_info = BusinessCommonTools().get_driver_info('PRO', a, sql, mysql_source)

    # 选择工作表，例如第一个工作表
    sheet = workbook.active
    # 假设我们要在第2行第3列（行列计数从1开始）写入数据""
    for b in driver_info:
        row = num
        print(b)
        print(row)
        print('==========')
        for key in list(b.keys()):
            if key=='driving_licence_issuing_authority':
                column = i[0] +1
                data = b['driving_licence_issuing_authority']
            if key=='issuing_authority':
                column = i[1] + 1
                data = b['issuing_authority']
            if key== 'cyzg_code':
                column = i[2] + 1
                data = b['cyzg_code']
            if key== 'dlys_code':
                column = i[3] + 1
                data = b['dlys_code']
            # 写入数据到指定单元格
            sheet.cell(row=row, column=column, value=data)
    num = num+1
    # 保存到一个新的文件，以避免覆盖原文件
    workbook.save(filename=new_path)